package zozopo.background.operator.operations;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import zozopo.database.BusiDatabaseManager;
import zozopo.database.SqlMachine;
import zozopo.operations.comm.OperatingService;
import zozopo.user.operations.UserTB;

public class OperatorlimitedOS  extends OperatingService{
	
	
	public OperatorlimitedOS(Connection conn){
		super(conn);
	}
	
	public OperatorlimitedOS(){
		this.databaseConnection = new BusiDatabaseManager();
		this.conn = databaseConnection.getConnection();
	}
	
	public static List<LimitedTB> limitedList; 
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<OperatorlimitedTB> selectListBySql(String sql){
		List<OperatorlimitedTB> list = null;
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		//Flow flow = new Flow(conn);
		//Map flowMap = flow.getFlowStateMapByKind(FlowCode.HOUSEWARE_RECEIPTLIST);
		if(resultSet != null){
				try {
					list = new ArrayList<OperatorlimitedTB>();
					while(resultSet.next()){
						OperatorlimitedTB obj = new OperatorlimitedTB();
						
						obj.setId_operatorlimited(resultSet.getInt(OperatorlimitedTB.ID_OPERATORLIMITED));
						
						obj.setMenulevel_operatorlimited(resultSet.getInt(OperatorlimitedTB.MENULEVEL_OPERATORLIMITED));
						
						obj.setLimitedcode_operatorlimited(resultSet.getString(OperatorlimitedTB.LIMITEDCODE_OPERATORLIMITED));
						obj.setLimitedid_operatorlimited(resultSet.getInt(OperatorlimitedTB.LIMITEDID_OPERATORLIMITED));
						obj.setLimitedkind_operatorlimited(resultSet.getString(OperatorlimitedTB.LIMITEDKIND_OPERATORLIMITED));
						obj.setLimitedname_operatorlimited(resultSet.getString(OperatorlimitedTB.LIMITEDNAME_OPERATORLIMITED));
						obj.setLimitedurl_operatorlimited(resultSet.getString(OperatorlimitedTB.LIMITEDURL_OPERATORLIMITED));
						
						obj.setOperatoremail_operatorlimited(resultSet.getString(OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED));
						obj.setOperatorid_operatorlimited(resultSet.getInt(OperatorlimitedTB.OPERATORID_OPERATORLIMITED)); 
						obj.setOperatorname_operatorlimited(resultSet.getString(OperatorlimitedTB.OPERATORNAME_OPERATORLIMITED)); 
						
						//obj.setMainname_operatorlimited(resultSet.getString(OperatorlimitedTB.MAINNAME_OPERATORLIMITED));
						//obj.setMainurl_operatorlimited(resultSet.getString(OperatorlimitedTB.MAINURL_OPERATORLIMITED));
						
						list.add(obj);
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return list;
	
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public boolean insert(OperatorlimitedTB operatorlimited){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> insertMap = new HashMap<String, Object>();

		insertMap.put(OperatorlimitedTB.MENULEVEL_OPERATORLIMITED, operatorlimited.getMenulevel_operatorlimited());
		
		insertMap.put(OperatorlimitedTB.LIMITEDCODE_OPERATORLIMITED, operatorlimited.getLimitedcode_operatorlimited());
		insertMap.put(OperatorlimitedTB.LIMITEDID_OPERATORLIMITED, operatorlimited.getLimitedid_operatorlimited());
		insertMap.put(OperatorlimitedTB.LIMITEDKIND_OPERATORLIMITED, operatorlimited.getLimitedkind_operatorlimited());
		insertMap.put(OperatorlimitedTB.LIMITEDNAME_OPERATORLIMITED, operatorlimited.getLimitedname_operatorlimited());
		insertMap.put(OperatorlimitedTB.LIMITEDURL_OPERATORLIMITED, operatorlimited.getLimitedurl_operatorlimited());
		insertMap.put(OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED, operatorlimited.getOperatoremail_operatorlimited());
		insertMap.put(OperatorlimitedTB.OPERATORID_OPERATORLIMITED, operatorlimited.getOperatorid_operatorlimited());
		insertMap.put(OperatorlimitedTB.OPERATORNAME_OPERATORLIMITED, operatorlimited.getOperatorname_operatorlimited());
	
		
		
		return sqlMachine.insertRow(OperatorlimitedTB.TABLE_NAME, insertMap);
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public boolean insert(LimitedTB limited,OperatorTB operator){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> insertMap = new HashMap<String, Object>();

		insertMap.put(OperatorlimitedTB.MENULEVEL_OPERATORLIMITED, limited.getMenulevel_limited());
		
		insertMap.put(OperatorlimitedTB.LIMITEDCODE_OPERATORLIMITED, limited.getCode_limited());
		insertMap.put(OperatorlimitedTB.LIMITEDID_OPERATORLIMITED, limited.getId_limited());
		insertMap.put(OperatorlimitedTB.LIMITEDKIND_OPERATORLIMITED, limited.getKind_limited());
		insertMap.put(OperatorlimitedTB.LIMITEDNAME_OPERATORLIMITED, limited.getName_limited());
		insertMap.put(OperatorlimitedTB.LIMITEDURL_OPERATORLIMITED, limited.getUrl_limited());
		insertMap.put(OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED, operator.getEmail_operator());
		insertMap.put(OperatorlimitedTB.OPERATORID_OPERATORLIMITED, operator.getId_operator());
		insertMap.put(OperatorlimitedTB.OPERATORNAME_OPERATORLIMITED, operator.getName_operator());
		
		//insertMap.put(OperatorlimitedTB.MAINNAME_OPERATORLIMITED, limited.getMainname_limited());
		//insertMap.put(OperatorlimitedTB.MAINURL_OPERATORLIMITED, limited.getMainurl_limited());
		
		
		return sqlMachine.insertRow(OperatorlimitedTB.TABLE_NAME, insertMap);
	}
	
	
	
	/**
	 * @version 1.0
	 * @param String operatorname,---> 用户名
	 * @author Shea
	 * @return boolean  
	 * @throws IOException 
	 * @see isExist(String operator,String password)--->判断用户是否存在
	 */
	
	public boolean isExist(String operatoreaml,int limitedid){
		boolean eResult = false;
		
		String sql = "select * from "+OperatorlimitedTB.TABLE_NAME+" where "+OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED+"='"+operatoreaml+"'"
		+ " AND " + OperatorlimitedTB.LIMITEDID_OPERATORLIMITED + "=" + limitedid;
		
		if(logger.isDebugEnabled()){
			logger.debug("isExist() sql:"+sql.toString());
		}
		try {
			ResultSet result = databaseConnection.executeQuery(conn,sql);
		
			if(result.next()){
				eResult = true;
			}
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return eResult;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<OperatorlimitedTB> selectByUserEmail(String operatoremail){
		String sql = "SELECT * FROM " + OperatorlimitedTB.TABLE_NAME
		+ " WHERE " + OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED
		+ "='" + operatoremail + "'";
		List<OperatorlimitedTB> list =  selectListBySql(sql);
		
		return list;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<OperatorlimitedTB> selectByLimitedcode(String limitedCode,int beginPageNum,int pageSize){
		String sql = "SELECT * FROM " + OperatorlimitedTB.TABLE_NAME
		+ " WHERE " + OperatorlimitedTB.LIMITEDCODE_OPERATORLIMITED
		+ "='" + limitedCode + "'"
		+" limit "+beginPageNum+","+pageSize; 
		List<OperatorlimitedTB> list =  selectListBySql(sql);
		
		return list;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<OperatorlimitedTB> selectAll(){
		String sql = "SELECT * FROM " + OperatorlimitedTB.TABLE_NAME;
		List<OperatorlimitedTB> list =  selectListBySql(sql);
		
		return list;
	}
	
	public List<OperatorlimitedTB> selectAll(int beginPageNum,int pageSize){
		
		List<OperatorlimitedTB> list = null;
		String sql = "SELECT * FROM " + OperatorlimitedTB.TABLE_NAME  
		+ " ORDER BY " + OperatorlimitedTB.ID_OPERATORLIMITED + " DESC "
		+ " limit "+beginPageNum+","+pageSize; 
		list = selectListBySql(sql);
		
		return list;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNum(){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + OperatorlimitedTB.TABLE_NAME ;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<OperatorlimitedTB> selectBySearchStr(String searchStr,int beginPageNum,int pageSize){
		String sql = "select * from "+OperatorlimitedTB.TABLE_NAME+" WHERE  "
		+OperatorlimitedTB.ID_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.LIMITEDCODE_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.LIMITEDNAME_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.LIMITEDURL_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.OPERATORNAME_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.OPERATORID_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"

		+" limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		return selectListBySql(sql);
	}
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForSearchStr(String searchStr){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + OperatorlimitedTB.TABLE_NAME +" WHERE  "
		+OperatorlimitedTB.ID_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.LIMITEDCODE_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.LIMITEDNAME_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.LIMITEDURL_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.OPERATORNAME_OPERATORLIMITED+" LIKE '%"+searchStr+"%'"
		+" OR "+OperatorlimitedTB.OPERATORID_OPERATORLIMITED+" LIKE '%"+searchStr+"%'";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForLimitedcode(String limitedCode){
		
		String sql = "select count(*) as rowNum from " + OperatorlimitedTB.TABLE_NAME 
		+ " WHERE " + OperatorlimitedTB.LIMITEDCODE_OPERATORLIMITED +"='"+ limitedCode +"'" ;
		
		int rowNum = 0;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @param String brand
	 * @author Shea
	 * @return ResultSet  
	 * @throws IOException 
	 */
	public boolean delete(String operatoremal,int limitedid){

		String sql = "delete from "+OperatorlimitedTB.TABLE_NAME+" where "+OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED+"='"+operatoremal+"'"
		+ " AND " + OperatorlimitedTB.LIMITEDID_OPERATORLIMITED + "=" + limitedid;
		
		return this.databaseConnection.executeUpdate(conn,sql);
	}
	
	/**
	 * @version 1.0
	 * @param String brand
	 * @author Shea
	 * @return ResultSet  
	 * @throws IOException 
	 */
	public boolean deleteAll(String operatoremal){

		String sql = "delete from "+OperatorlimitedTB.TABLE_NAME+" where "+OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED+"='"+operatoremal+"'";
		
		return this.databaseConnection.executeUpdate(conn,sql);
	}
	
	
	
	public Map<String,OperatorlimitedTB> getUserlimitedMap(OperatorTB operator){
		
		List<OperatorlimitedTB> list = null;
		Map<String,OperatorlimitedTB> map = new LinkedHashMap<String,OperatorlimitedTB>();
		String sql = "SELECT * FROM " + OperatorlimitedTB.TABLE_NAME  
		
		+ " WHERE " + OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED +"='"+operator.getEmail_operator()+"'"
		+ " ORDER BY " + OperatorlimitedTB.ID_OPERATORLIMITED;

		list = selectListBySql(sql);
		
		for(OperatorlimitedTB operatorlimited:list){
			map.put(operatorlimited.getLimitedcode_operatorlimited(), operatorlimited);
		}
		
		return map;
		
	}
	
	public Map<String,List<LimitedTB>> getLimitedTree(OperatorTB operator){//未修正
		
		LimitedOS limitedOS = new LimitedOS(conn);
		
		Map<String,LimitedTB> limitedMap = limitedOS.selectLimitedMap();
		Map<String,List<LimitedTB>> treeMap = new LinkedHashMap<String,List<LimitedTB>>();
		
		List<OperatorlimitedTB> parentList = null;
		//Map<String,OperatorlimitedTB> map = new LinkedHashMap<String,OperatorlimitedTB>();
		String parentSql = "SELECT * FROM " + OperatorlimitedTB.TABLE_NAME  
		+ " WHERE " + OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED +"='"+operator.getEmail_operator()+"'"
		+ " AND " + OperatorlimitedTB.MENULEVEL_OPERATORLIMITED +"=0 "
		+ " ORDER BY " + OperatorlimitedTB.ID_OPERATORLIMITED;

		parentList = selectListBySql(parentSql);
		
		for(OperatorlimitedTB operatorlimited:parentList){
			List<LimitedTB> kindList = new ArrayList<LimitedTB>();
			kindList.add(limitedMap.get(operatorlimited.getLimitedcode_operatorlimited()));
			treeMap.put(operatorlimited.getLimitedcode_operatorlimited(), kindList);
		}
		 
		String sql = "SELECT * FROM " + OperatorlimitedTB.TABLE_NAME  
		
		+ " WHERE " + OperatorlimitedTB.OPERATOREMAIL_OPERATORLIMITED +"='"+operator.getEmail_operator()+"'"
		+ " ORDER BY " + OperatorlimitedTB.ID_OPERATORLIMITED;
		
		List<OperatorlimitedTB> list = null;
		list = selectListBySql(sql);
		
		
		for(OperatorlimitedTB operatorlimited:list){
			if(operatorlimited.getMenulevel_operatorlimited() > 0){
				if(treeMap.containsKey(operatorlimited.getLimitedkind_operatorlimited())){
					List<LimitedTB> kindList = treeMap.get(operatorlimited.getLimitedkind_operatorlimited());
					kindList.add(limitedMap.get(operatorlimited.getLimitedcode_operatorlimited()));
					treeMap.put(operatorlimited.getLimitedkind_operatorlimited(), kindList);
				}
			}
			//map.put(operatorlimited.getLimitedcode_operatorlimited(), operatorlimited);
		}
		
		return treeMap;
	}

}
